package com.x.erp.dao;

import com.x.erp.model.Car;
import com.x.erp.model.ClientSelectTemplate;
import com.x.erp.util.ConstantState;
import com.x.framework.dao.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * C端的车辆信息接口
 */
@Repository
public class ClientCarDao extends BaseDao {

	/**
	 * 通过客户端订阅信息查询对应的车辆信息列表
	 *
	 * @param
	 * @return 满足条件的carlist
	 * @throws Exception
	 */
	private final static String selectCarListBySelectTemplateSql = "SELECT " +
			"ec.CAR_ID, "+
			"ec.CAR_TITLE, "+
			"ec.CAR_IMAGE_URL, "+
			"ec.CAR_MILEAGE, "+
			"ec.CAR_DISPLACEMENT, "+
			"ec.CAR_INLET_MODEL, "+
			"ec.CAR_GEARBOX, "+
			"ec.CAR_RETAIL_PRICE, "+
			"ec.CAR_GUIDE_PRICE, "+
			"ec.CAR_LICENCE_DATE, "+
			"ec.CAR_LICENCE_STATE, "+
			"ec.CAR_AUTH_STATE, "+
			"ec.MODEL_ID, "+
			"ec.SERIES_ID, "+
			"ec.BRAND_ID, "+
			"ec.SHOP_ID, "+
			"ec.MARKET_ID, "+
			"ec.CAR_FINANCE_SERVICE, " +
			"ec.CAR_EXTENDED_WARRANTY, " +
			"eu.USER_MOBILE, "+
			"em.MARKET_NAME, "+
			"em.MARKET_SHORT_NAME, "+
			"es.SHOP_NAME, "+
			"es.SHOP_SHORT_NAME, "+
			"bc.CITY_NAME " +
			"FROM " +
			"ERP_CAR ec " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.BASE_MODEL bm ON ec.MODEL_ID = bm.MODEL_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN BASE_CITY bc ON bc.CITY_ID = em.CITY_ID " +
			"LEFT JOIN ERP_USER eu ON ec.SALE_USER_ID = eu.USER_ID " +
			"LEFT JOIN ERP_CAR_CONF_INFO ecci ON ec.CAR_ID = ecci.CAR_ID " +
			"WHERE ec.CAR_SALE_STATE = ? "; // 只查询已上架的车辆

	public List<Car> selectCarListBySelectTemplate(Car car, ClientSelectTemplate clientSelectTemplate) throws Exception {
		StringBuilder sql = new StringBuilder(selectCarListBySelectTemplateSql);
		List<Object> params = new ArrayList<>();
		// 只查询已上架的车辆
		params.add(ConstantState.CAR_SALE_STATE_ONSALE_FINISH);
		searchCondition(sql, params, car);
		searchCarListByClientSelectTemplate(sql, params, clientSelectTemplate);
		sql.append("GROUP BY ec.CAR_ID ");
		// 亮点配置不为空时 sql添加HAVING条件
		appendHavingCondition(sql, params, clientSelectTemplate);
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private void appendHavingCondition(StringBuilder sql, List<Object> params, ClientSelectTemplate clientSelectTemplate) {
		if(clientSelectTemplate != null){
			// 亮点配置
			String configId = clientSelectTemplate.getTemplateConfigId();
			if(this.isString(configId)){
				String[] configIds = configId.split(",");
				if(configIds.length > 0){
					sql.append("HAVING COUNT(ec.CAR_ID) = ? ");
					params.add(configIds.length);
				}
			}
		}
	}

	private final static String selectCarListSql = "SELECT " +
			"ec.CAR_ID, "+
			"ec.CAR_TITLE, "+
			"ec.CAR_IMAGE_URL, "+
			"ec.CAR_GEARBOX, "+
			"ec.CAR_MILEAGE, "+
			"ec.CAR_RETAIL_PRICE, "+
			"ec.CAR_GUIDE_PRICE, "+
			"ec.CAR_LICENCE_DATE, "+
			"ec.CAR_AUTH_STATE, "+
			"ec.SHOP_ID, "+
			"ec.MARKET_ID, "+
			"bb.BRAND_NAME, "+
			"bc.CITY_NAME " +
			"FROM " +
			"ERP_CAR ec " +
			"JOIN x_base.BASE_BRAND bb ON ec.BRAND_ID = bb.BRAND_ID " +
			"JOIN x_base.BASE_SERIES bs ON ec.SERIES_ID = bs.SERIES_ID " +
			"JOIN x_base.BASE_MODEL bm ON ec.MODEL_ID = bm.MODEL_ID " +
			"JOIN ERP_MARKET em ON ec.MARKET_ID = em.MARKET_ID " +
			"JOIN ERP_SHOP es ON ec.SHOP_ID = es.SHOP_ID " +
			"LEFT JOIN base_city bc ON bc.city_id = em.city_id " +
			"WHERE ec.CAR_SALE_STATE = ? "; // 只查询已上架的车辆

	/**
	 * C端查询车辆列表信息
	 * @param car 封装了car的检索条件
	 * @return 满足条件的carlist
	 * @throws Exception
	 */
	public List<Car> selectCarList(Car car) throws Exception{
		StringBuilder sql = new StringBuilder(selectCarListSql);
		List<Object> params = new ArrayList<>();
		// 只查询已上架的车辆
		params.add(ConstantState.CAR_SALE_STATE_ONSALE_FINISH);
		searchCondition(sql, params, car);
		orderCondition(sql, car);
		return this.queryForList(sql.toString(), Car.class, params, car);
	}

	private void searchCondition(StringBuilder sql, List<Object> params, Car car) {
		if(car != null){
			// 店铺Id
			if(this.isString(car.getMarketId())){
				sql.append("AND ec.MARKET_ID = ? ");
				params.add(car.getMarketId());
			}
			// 店铺Id
			if(this.isString(car.getShopId())){
				sql.append("AND ec.SHOP_ID = ? ");
				params.add(car.getShopId());
			}
			// 品牌Id
			if(car.getBrandId() != null){
				sql.append("AND ec.BRAND_ID = ? ");
				params.add(car.getBrandId());
			}
			// 车身类型Id
			if(car.getCarTypeId() != null){
				sql.append("AND ec.CAR_TYPE_ID = ? ");
				params.add(car.getCarTypeId());
			}
			// 外观颜色
			if(this.isString(car.getCarOutColor())){
				sql.append("AND ec.CAR_OUT_COLOR = ? ");
				params.add(car.getCarOutColor());
			}
			// 环保等级
			if(this.isString(car.getCarEnviron())){
				sql.append("AND ec.CAR_ENVIRON = ? ");
				params.add(car.getCarEnviron());
			}
			// 车型Id
			if(car.getModelId() != null){
				sql.append("AND ec.MODEL_ID = ? ");
				params.add(car.getModelId());
			}
			// 车系Id
			if(car.getSeriesId() != null){
				sql.append("AND ec.SERIES_ID = ? ");
				params.add(car.getSeriesId());
			}
			// 认证状态
			if(car.getCarAuthState() != null){
				sql.append("AND ec.CAR_AUTH_STATE = ? ");
				params.add(car.getCarAuthState());
			}
			// 起始价格
			if(car.getFromRetailPrice() != null) {
				sql.append("AND ec.CAR_RETAIL_PRICE >= ? ");
				params.add(car.getFromRetailPrice());
			}
			// 结束价格
			if(car.getToRetailPrice() != null) {
				sql.append("AND ec.CAR_RETAIL_PRICE <= ? ");
				params.add(car.getToRetailPrice());
			}
			// 起始里程
			if(car.getFromMileage() != null) {
				sql.append("AND ec.CAR_MILEAGE >= ? ");
				params.add(car.getFromMileage());
			}
			// 结束里程
			if(car.getToMileage() != null) {
				sql.append("AND ec.CAR_MILEAGE <= ? ");
				params.add(car.getToMileage());
			}
			// 起始车龄
			if(car.getFromCarAge() != null) {
				sql.append("AND ec.CAR_LICENCE_DATE <= ? ");
				Calendar c = Calendar.getInstance();
				c.add(Calendar.YEAR, -car.getFromCarAge());
				params.add(c.getTime());
			}
			// 结束车龄
			if(car.getToCarAge() != null) {
				sql.append("AND ec.CAR_LICENCE_DATE >= ? ");
				Calendar c = Calendar.getInstance();
				c.add(Calendar.YEAR, -car.getToCarAge());
				params.add(c.getTime());
			}
		}
	}

	/**
	 * 排序
	 * @param sql
	 * @param car
	 */
	private void orderCondition(StringBuilder sql, Car car) {
		if(StringUtils.isNotEmpty(car.getOrderColunm())) {
			sql.append("ORDER BY " + car.getOrderColunm() + " ");
			if(StringUtils.isNotEmpty(car.getOrderMode())) {
				sql.append(car.getOrderMode() + " ");
			}
		}else {
			sql.append("ORDER BY ec.CAR_CREATE_TIME DESC ");
		}
	}

	// 拼接客户C端订阅条件
	private void searchCarListByClientSelectTemplate(StringBuilder sql, List<Object> params, ClientSelectTemplate clientSelectTemplate){
		if(clientSelectTemplate != null){
			// 城市
			String cityId = clientSelectTemplate.getTemplateCityId();
			if(this.isString(cityId)){
				String[] cityIdS = cityId.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < cityIdS.length; i++) {
					if (i == cityIdS.length - 1) {
						sql.append("bc.CITY_ID = ? ) ");
						params.add(cityIdS[i]);
					} else {
						sql.append("bc.CITY_ID = ? OR ");
						params.add(cityIdS[i]);
					}
				}
			}
			// 品牌
			String brandId = clientSelectTemplate.getTemplateBrandId();
			if(this.isString(brandId)){
				String[] brandIds = brandId.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < brandIds.length; i++) {
					if (i == brandIds.length - 1) {
						sql.append("ec.BRAND_ID = ? ) ");
						params.add(brandIds[i]);
					} else {
						sql.append("ec.BRAND_ID = ? OR ");
						params.add(brandIds[i]);
					}
				}
			}
			// 车系
			String seriesId = clientSelectTemplate.getTemplateSeriesId();
			if(this.isString(seriesId)){
				String[] seriesIds = seriesId.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < seriesIds.length; i++) {
					if (i == seriesIds.length - 1) {
						sql.append("ec.SERIES_ID = ? ) ");
						params.add(seriesIds[i]);
					} else {
						sql.append("ec.SERIES_ID = ? OR ");
						params.add(seriesIds[i]);
					}
				}
			}
			// 起始价格
			BigDecimal priceFrom = clientSelectTemplate.getTemplatePriceFrom();
			if(this.isNotNull(priceFrom)){
				sql.append("AND ec.CAR_RETAIL_PRICE >= ? ");
				params.add(priceFrom);
			}
			// 结束价格
			BigDecimal priceTo = clientSelectTemplate.getTemplatePriceTo();
			if(this.isNotNull(priceTo)){
				sql.append("AND ec.CAR_RETAIL_PRICE <= ? ");
				params.add(priceTo);
			}
			// 起始车龄
			Integer carAgeFrom = clientSelectTemplate.getTemplateCarAgeFrom();
			if(this.isNotNull(carAgeFrom)){
				sql.append("AND ec.CAR_LICENCE_DATE <= ? ");
				Calendar c = Calendar.getInstance();
				c.add(Calendar.YEAR, -carAgeFrom);
				params.add(c.getTime());
			}
			// 结束车龄
			Integer carAgeTo = clientSelectTemplate.getTemplateCarAgeTo();
			if(this.isNotNull(carAgeTo)){
				sql.append("AND ec.CAR_LICENCE_DATE >= ? ");
				Calendar c = Calendar.getInstance();
				c.add(Calendar.YEAR, -carAgeTo);
				params.add(c.getTime());
			}
			// 起始里程
			BigDecimal mileageFrom = clientSelectTemplate.getTemplateMileageFrom();
			if(this.isNotNull(mileageFrom)){
				sql.append("AND ec.CAR_MILEAGE >= ? ");
				params.add(mileageFrom);
			}
			// 结束里程
			BigDecimal mileageTo = clientSelectTemplate.getTemplateMileageTo();
			if(this.isNotNull(mileageTo)){
				sql.append("AND ec.CAR_MILEAGE <= ? ");
				params.add(mileageTo);
			}
			// 变数箱
			String gearbox = clientSelectTemplate.getTemplateGearbox();
			if(this.isString(gearbox)){
				String[] gearboxs = gearbox.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < gearboxs.length; i++) {
					if (i == gearboxs.length - 1) {
						sql.append("ec.CAR_GEARBOX = ? ) ");
						params.add(gearboxs[i]);
					} else {
						sql.append("ec.CAR_GEARBOX = ? OR ");
						params.add(gearboxs[i]);
					}
				}
			}
			// 外观颜色
			String outColor = clientSelectTemplate.getTemplateOutColor();
			if(this.isString(outColor)){
				String[] outColors = outColor.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < outColors.length; i++) {
					if (i == outColors.length - 1) {
						sql.append("ec.CAR_OUT_COLOR = ? ) ");
						params.add(outColors[i]);
					} else {
						sql.append("ec.CAR_OUT_COLOR = ? OR ");
						params.add(outColors[i]);
					}
				}
			}
			// 车身类型
			String carTypeId = clientSelectTemplate.getTemplateCarTypeId();
			if(this.isString(carTypeId)){
				String[] carTypeIds = carTypeId.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < carTypeIds.length; i++) {
					if (i == carTypeIds.length - 1) {
						sql.append("ec.CAR_TYPE_ID = ? ) ");
						params.add(carTypeIds[i]);
					} else {
						sql.append("ec.CAR_TYPE_ID = ? OR ");
						params.add(carTypeIds[i]);
					}
				}
			}
			// 环保类型
			String environ = clientSelectTemplate.getTemplateEnviron();
			if(this.isString(environ)){
				String[] environs = environ.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < environs.length; i++) {
					if (i == environs.length - 1) {
						sql.append("ec.CAR_ENVIRON = ? ) ");
						params.add(environs[i]);
					} else {
						sql.append("ec.CAR_ENVIRON = ? OR ");
						params.add(environs[i]);
					}
				}
			}
			// 亮点配置
			String configId = clientSelectTemplate.getTemplateConfigId();
			if(this.isString(configId)){
				String[] configIds = configId.split(",");
				sql.append("AND ( ");
				for (int i = 0; i < configIds.length; i++) {
					if (i == configIds.length - 1) {
						sql.append("ecci.CONF_ID = ? ) ");
						params.add(configIds[i]);
					} else {
						sql.append("ecci.CONF_ID = ? OR ");
						params.add(configIds[i]);
					}
				}
			}
		}
	}
}
